﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace Snapshot
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            //RunOne();
            //RunTwo();
            RunDmcWithTrans();
        }

        private static void RunOne()
        {
            // Assumes GetConnectionString returns a valid connection string
            // where pooling is turned off by setting Pooling=False;. 
            var connectionString = GetConnectionString();
            using (var connection1 = new SqlConnection(connectionString))
            {
                // Drop the TestSnapshot table if it exists
                connection1.Open();
                var command1 = connection1.CreateCommand();
                command1.CommandText = "IF EXISTS "
                                       + "(SELECT * FROM sys.tables WHERE name=N'TestSnapshot') "
                                       + "DROP TABLE TestSnapshot";
                try
                {
                    command1.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                // Enable Snapshot isolation
                command1.CommandText = "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON";
                command1.ExecuteNonQuery();

                // Create a table named TestSnapshot and insert one row of data
                command1.CommandText = "CREATE TABLE TestSnapshot (ID int primary key, valueCol int)";
                command1.ExecuteNonQuery();
                command1.CommandText = "INSERT INTO TestSnapshot VALUES (1,1)";
                command1.ExecuteNonQuery();

                // Begin, but do not complete, a transaction to update the data 
                // with the Serializable isolation level, which locks the table
                // pending the commit or rollback of the update. The original 
                // value in valueCol was 1, the proposed new value is 22.
                var transaction1 = connection1.BeginTransaction(IsolationLevel.Serializable);
                command1.Transaction = transaction1;
                command1.CommandText = "UPDATE TestSnapshot SET valueCol=22 WHERE ID=1";
                command1.ExecuteNonQuery();

                // Open a second connection to AdventureWorks
                using (var connection2 = new SqlConnection(connectionString))
                {
                    connection2.Open();
                    // Initiate a second transaction to read from TestSnapshot
                    // using Snapshot isolation. This will read the original 
                    // value of 1 since transaction1 has not yet committed.
                    var command2 = connection2.CreateCommand();
                    var transaction2 = connection2.BeginTransaction(IsolationLevel.Snapshot);
                    command2.Transaction = transaction2;
                    command2.CommandText = "SELECT ID, valueCol FROM TestSnapshot";
                    var reader2 = command2.ExecuteReader();
                    while (reader2.Read())
                    {
                        Console.WriteLine("Expected 1,1 Actual "
                                          + reader2.GetValue(0)
                                          + "," + reader2.GetValue(1));
                    }
                    transaction2.Commit();
                }

                // Open a third connection to AdventureWorks and
                // initiate a third transaction to read from TestSnapshot
                // using ReadCommitted isolation level. This transaction
                // will not be able to view the data because of 
                // the locks placed on the table in transaction1
                // and will time out after 4 seconds.
                // You would see the same behavior with the
                // RepeatableRead or Serializable isolation levels.
                using (var connection3 = new SqlConnection(connectionString))
                {
                    connection3.Open();
                    var command3 = connection3.CreateCommand();
                    var transaction3 =
                        connection3.BeginTransaction(IsolationLevel.ReadCommitted);
                    command3.Transaction = transaction3;
                    command3.CommandText = "SELECT ID, valueCol FROM TestSnapshot";
                    command3.CommandTimeout = 4;
                    try
                    {
                        var sqldatareader3 = command3.ExecuteReader();
                        while (sqldatareader3.Read())
                        {
                            Console.WriteLine("You should never hit this.");
                        }
                        transaction3.Commit();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Expected timeout expired exception: " + ex.Message);
                        transaction3.Rollback();
                    }
                }

                // Open a fourth connection to AdventureWorks and
                // initiate a fourth transaction to read from TestSnapshot
                // using the ReadUncommitted isolation level. ReadUncommitted
                // will not hit the table lock, and will allow a dirty read  
                // of the proposed new value 22 for valueCol. If the first
                // transaction rolls back, this value will never actually have
                // existed in the database.
                using (var connection4 = new SqlConnection(connectionString))
                {
                    connection4.Open();
                    var command4 = connection4.CreateCommand();
                    var transaction4 = connection4.BeginTransaction(IsolationLevel.ReadUncommitted);
                    command4.Transaction = transaction4;
                    command4.CommandText = "SELECT ID, valueCol FROM TestSnapshot";
                    var reader4 = command4.ExecuteReader();
                    while (reader4.Read())
                    {
                        Console.WriteLine("Expected 1,22 Actual " + reader4.GetValue(0) + "," + reader4.GetValue(1));
                    }

                    transaction4.Commit();
                }

                // Roll back the first transaction
                transaction1.Rollback();
            }

            // CLEANUP
            // Delete the TestSnapshot table and set
            // ALLOW_SNAPSHOT_ISOLATION OFF
            using (var connection5 = new SqlConnection(connectionString))
            {
                connection5.Open();
                var command5 = connection5.CreateCommand();
                command5.CommandText = "DROP TABLE TestSnapshot";
                var command6 = connection5.CreateCommand();
                command6.CommandText = "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
                try
                {
                    command5.ExecuteNonQuery();
                    command6.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
            Console.WriteLine("Done!");
        }

        private static void RunTwo()
        {
            // Assumes GetConnectionString returns a valid connection string
            // where pooling is turned off by setting Pooling=False;. 
            var connectionString = GetConnectionString();
            using (var connection1 = new SqlConnection(connectionString))
            {
                connection1.Open();
                var command1 = connection1.CreateCommand();

                // Enable Snapshot isolation in AdventureWorks
                command1.CommandText = "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON";
                try
                {
                    command1.ExecuteNonQuery();
                    Console.WriteLine("Snapshot Isolation turned on in AdventureWorks.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("ALLOW_SNAPSHOT_ISOLATION ON failed: {0}", ex.Message);
                    throw;
                }

                // Create a table 
                command1.CommandText =
                    "IF EXISTS "
                    + "(SELECT * FROM sys.tables "
                    + "WHERE name=N'TestSnapshotUpdate')"
                    + " DROP TABLE TestSnapshotUpdate";

                command1.ExecuteNonQuery();
                command1.CommandText = "CREATE TABLE TestSnapshotUpdate (ID int primary key, CharCol nvarchar(100));";

                try
                {
                    command1.ExecuteNonQuery();
                    Console.WriteLine("TestSnapshotUpdate table created.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("CREATE TABLE failed: {0}", ex.Message);
                    throw;
                }

                // Insert some data
                command1.CommandText =
                    "INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');"
                    + "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');"
                    + "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');";
                try
                {
                    command1.ExecuteNonQuery();
                    Console.WriteLine("Data inserted TestSnapshotUpdate table.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    throw;
                }

                // Begin, but do not complete, a transaction 
                // using the Snapshot isolation level.
                SqlTransaction transaction1 = null;
                try
                {
                    transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot);
                    command1.CommandText = "SELECT * FROM TestSnapshotUpdate WHERE ID BETWEEN 1 AND 3";
                    command1.Transaction = transaction1;
                    command1.ExecuteNonQuery();
                    Console.WriteLine("Snapshot transaction1 started.");

                    // Open a second Connection/Transaction to update data
                    // using ReadCommitted. This transaction should succeed.
                    using (var connection2 = new SqlConnection(connectionString))
                    {
                        connection2.Open();
                        var command2 = connection2.CreateCommand();
                        command2.CommandText = "UPDATE TestSnapshotUpdate SET CharCol="
                                               + "N'New value from Connection2' WHERE ID=1";
                        var transaction2 =
                            connection2.BeginTransaction(IsolationLevel.ReadCommitted);
                        command2.Transaction = transaction2;
                        try
                        {
                            command2.ExecuteNonQuery();
                            transaction2.Commit();
                            Console.WriteLine("transaction2 has modified data and committed.");
                        }
                        catch (SqlException ex)
                        {
                            Console.WriteLine(ex.Message);
                            transaction2.Rollback();
                        }
                        finally
                        {
                            transaction2.Dispose();
                        }
                    }

                    // Now try to update a row in Connection1/Transaction1.
                    // This transaction should fail because Transaction2
                    // succeeded in modifying the data.
                    command1.CommandText =
                        "UPDATE TestSnapshotUpdate SET CharCol="
                        + "N'New value from Connection1' WHERE ID=1";
                    command1.Transaction = transaction1;
                    command1.ExecuteNonQuery();
                    transaction1.Commit();
                    Console.WriteLine("You should never see this.");
                }
                catch (SqlException ex)
                {
                    Console.WriteLine("Expected failure for transaction1:");
                    Console.WriteLine("{0}: {1}", ex.Number, ex.Message);
                }
                finally
                {
                    transaction1?.Dispose();
                }
            }

            // CLEANUP:
            // Turn off Snapshot isolation and delete the table
            using (var connection3 = new SqlConnection(connectionString))
            {
                connection3.Open();
                var command3 = connection3.CreateCommand();
                command3.CommandText = "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
                try
                {
                    command3.ExecuteNonQuery();
                    Console.WriteLine("CLEANUP: Snapshot isolation turned off in AdventureWorks.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("CLEANUP FAILED: {0}", ex.Message);
                }

                command3.CommandText = "DROP TABLE TestSnapshotUpdate";

                try
                {
                    command3.ExecuteNonQuery();
                    Console.WriteLine("CLEANUP: TestSnapshotUpdate table deleted.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("CLEANUP FAILED: {0}", ex.Message);
                    throw;
                }
            }
        }

        private static void RunDmcWithTrans()
        {
            var user1Connection = new SqlConnection(GetConnectionString());
            user1Connection.Open();

            var user2Connection = new SqlConnection(GetConnectionString());
            user2Connection.Open();

            var user3Connection = new SqlConnection(GetConnectionString());
            user3Connection.Open();

            var sqlcom = new SqlCommand("IF EXISTS "
                                   + "(SELECT * FROM sys.tables WHERE name=N'snapTest') "
                                   + "DROP TABLE snapTest", user1Connection);
            sqlcom.ExecuteNonQuery();

            var sqlcom1 = new SqlCommand("CREATE TABLE snapTest ([id] INT IDENTITY, col1 VARCHAR(15))", user1Connection);
            sqlcom1.ExecuteNonQuery();

            var sqlcom2 = new SqlCommand("INSERT INTO snapTest VALUES('Niels')", user1Connection);
            sqlcom2.ExecuteNonQuery();

            var user3StepZ1 = new SqlCommand("SET TRANSACTION ISOLATION " +
                                         "LEVEL SNAPSHOT " +
                                         "BEGIN TRAN " +
                                         "SELECT col1 FROM snapTest WHERE id = 1", user3Connection);
            var user3StepZ1Read = user3StepZ1.ExecuteReader(CommandBehavior.SingleRow);
            
            var user1Step1 = new SqlCommand("SET TRANSACTION ISOLATION " +
                                         "LEVEL SNAPSHOT " +
                                         "BEGIN TRAN " +
                                         "UPDATE snapTest " +
                                         "SET col1 = 'NewNiels' " +
                                         "WHERE id = 1 ", user1Connection);
            user1Step1.ExecuteNonQuery();

            var user2Step2 = new SqlCommand("SET TRANSACTION ISOLATION LEVEL SNAPSHOT " +
                                            "BEGIN TRAN " +
                                            "SELECT col1 FROM snapTest " +
                                            "WHERE id = 1", user2Connection); //receives value 'Niels'
            user2Step2.ExecuteNonQuery();

            var user1Step3 = new SqlCommand("COMMIT TRAN", user1Connection);
            user1Step3.ExecuteNonQuery();

            var user2Step4 = new SqlCommand("SELECT col1 FROM snapTest WHERE id = 1", user1Connection); //receives value 'Niels'
            var user2Step4Read = user2Step4.ExecuteReader(CommandBehavior.SingleRow);
            user2Step4Read.Read();
            Console.WriteLine(user2Step4Read["col1"]);
            user2Step4Read.Close();

            var user2Step5 = new SqlCommand("COMMIT TRAN", user2Connection);
            user2Step5.ExecuteNonQuery();

            var user2Step6 = new SqlCommand("SELECT col1 FROM snapTest WHERE id = 1", user2Connection); //receives value 'NewNiels'
            var user2Step6Read = user2Step6.ExecuteReader(CommandBehavior.SingleRow);
            user2Step6Read.Read();
            Console.WriteLine(user2Step6Read["col1"]);
            user2Step6Read.Close();

            user3StepZ1Read.Read();
            Console.WriteLine(user3StepZ1Read["col1"]);
            user3StepZ1Read.Close();

            var user3StepZ2 = new SqlCommand("COMMIT TRAN", user3Connection);
            user3StepZ2.ExecuteNonQuery();

            user1Connection.Dispose();
            user2Connection.Dispose();
            user3Connection.Dispose();
        }

        private static void RunDmcWithoutTrans()
        {
            var user1Connection = new SqlConnection(GetConnectionString());
            user1Connection.Open();

            var user2Connection = new SqlConnection(GetConnectionString());
            user2Connection.Open();

            var user3Connection = new SqlConnection(GetConnectionString());
            user3Connection.Open();

            var sqlcom = new SqlCommand("IF EXISTS "
                                   + "(SELECT * FROM sys.tables WHERE name=N'snapTest') "
                                   + "DROP TABLE snapTest", user1Connection);
            sqlcom.ExecuteNonQuery();

            var sqlcom1 = new SqlCommand("CREATE TABLE snapTest ([id] INT IDENTITY, col1 VARCHAR(15))", user1Connection);
            sqlcom1.ExecuteNonQuery();

            var sqlcom2 = new SqlCommand("INSERT INTO snapTest VALUES('Niels')", user1Connection);
            sqlcom2.ExecuteNonQuery();

            var user3StepZ1 = new SqlCommand("SET TRANSACTION ISOLATION " +
                                         "LEVEL SNAPSHOT " +
                                         "BEGIN TRAN " +
                                         "SELECT col1 FROM snapTest WHERE id = 1", user3Connection);
            var user3StepZ1Read = user3StepZ1.ExecuteReader(CommandBehavior.SingleRow);

            var user1Step1 = new SqlCommand(
                                         "UPDATE snapTest " +
                                         "SET col1 = 'NewNiels' " +
                                         "WHERE id = 1 ", user1Connection);
            user1Step1.ExecuteNonQuery();

            var user2Step2 = new SqlCommand(
                                            "SELECT col1 FROM snapTest " +
                                            "WHERE id = 1", user2Connection); //receives value 'Niels'
            user2Step2.ExecuteNonQuery();

            var user2Step4 = new SqlCommand("SELECT col1 FROM snapTest WHERE id = 1", user1Connection); //receives value 'Niels'
            var user2Step4Read = user2Step4.ExecuteReader(CommandBehavior.SingleRow);
            user2Step4Read.Read();
            Console.WriteLine(user2Step4Read["col1"]);
            user2Step4Read.Close();

            var user2Step6 = new SqlCommand("SELECT col1 FROM snapTest WHERE id = 1", user2Connection); //receives value 'NewNiels'
            var user2Step6Read = user2Step6.ExecuteReader(CommandBehavior.SingleRow);
            user2Step6Read.Read();
            Console.WriteLine(user2Step6Read["col1"]);
            user2Step6Read.Close();

            user3StepZ1Read.Read();
            Console.WriteLine(user3StepZ1Read["col1"]);
            user3StepZ1Read.Close();

            var user3StepZ2 = new SqlCommand("COMMIT TRAN", user3Connection);
            user3StepZ2.ExecuteNonQuery();

            user1Connection.Dispose();
            user2Connection.Dispose();
            user3Connection.Dispose();
        }

        private static string GetConnectionString() => "MyConnectionString";
    }
}